﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Text;
using EasyUIDemo.Model;
using System.Data;
using EasyUIDemo1.ViewModels;
using System.Linq.Expressions;
using System.IO;

using Husb.Common;

namespace EasyUIDemo1.Controllers
{
    public class DatabaseController : Controller
    {
        //
        // GET: /Database/

        public ActionResult Index()
        {
            String fieldString = @"PersonCode 
Name 
Gender 
UnitCode 
Units 
CompanyOwnership
IndustryCode
IndustryType
DateOfBirth
IdentityType
WorkingHours 
ContinuousDays
ExcludingService1
ExcludingService2
Education 
TimeInSchool 
SecondDegree 
GraduationDay 
CurrentPosition 
CurrentPositionAt 
OriginalPosition 
OriginalPositionsAt
CurrentTitleCode
CurrentTitles 
CurrentTitleDate 
OriginalTitleCode 
OriginalTitle 
OriginalTitleAt 
IncentivePayAt 
FixedSalaryAt
VariableSalaryAt
PositionGrade1993
PositionSalary1993
LevelGrade1993
LevelOfSalarys1993
SenioritySalary1993 
BaseSalary1993 
IsEnjoy10 
OriginalPositionSalary 
BaseSalary 
PositionGradeCode 
PositionGrade 
PositionGradeSalary 
PositionGradeAt 
GradeLevelCode
GradeLevel 
LevelSalary
GradeLevelAt
FloatGradeCode
FloatGrade
FloatingSalarys
FloatGradeAt
SenioritySalary 
AllowanceBonus 
LocalSubsidies 
RankAllowance 
OnlyChildSubsidies
SpecialPostAllowance 
HousingSubsidies 
PostSubsidies 
AwardForPost
ThreePosSubsidies
SubsidiesForNursingEducationAge 
TotalSalarys 
Remark
RaisingDueTo 
RaiseAt 
ReduceDueTo
ReduceAt
Resident
IsEnjoy101993
LengthOfService 
ExecutionAt
SubsidyA
SubsidyB
RemarkA";
            StringBuilder sb = new StringBuilder("");
            sb.Append(" CREATE TABLE Salaries ( ");
            string[] fields = fieldString.Split('\n');
            foreach(String f in fields)
            {
                sb.Append(f.Trim('\r').Trim()).Append(" nvarchar(384), <br />");
            }
            sb.Append(')');
            foreach (String f in fields)
            {
                sb.Append(f.Trim('\r').Trim()).Append(", ");
            }
            ViewBag.Message = sb.ToString();
            return View();
        }

        public ActionResult GenerateIndustrySQL()
        {
            Industry s = new EasyUIDemo.Model.Industry();

            StringBuilder sb = new StringBuilder("");
            DataSet ds = SQLiteHelper.ExecuteDataSet("SELECT DISTINCT  [行业代码] ,[行业类型] FROM [Salary] ORDER BY [行业代码]");
            // "INSERT INTO [Admin].[Industries]([Name],[IndustryCode]) VALUES () ;"

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                if (row[1].ToString().Trim().Length > 0)
                {
                    sb.Append("INSERT INTO [Admin].[Industries]([Name],[IndustryCode]) VALUES ('")
                        .Append(row[1].ToString().Trim()).Append("', '")
                        .Append(row[0].ToString().Trim()).Append("');").Append("<br />");//System.Environment.NewLine
                }
            }

            ViewBag.Message = sb.ToString();
            return View("GenerateSQL");
        }

        public ActionResult GenerateCategorySQL()
        {
            StringBuilder sb = new StringBuilder("");
            
            DataSet ds = SQLiteHelper.ExecuteDataSet("SELECT DISTINCT [单位性质] FROM [Salary]");
            // "INSERT INTO [Admin].[Industries]([Name],[IndustryCode]) VALUES () ;"

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                if (row[0].ToString().Trim().Length > 0)
                {
                    sb.Append("INSERT INTO [Admin].[Categories] ([Name]) VALUES ('")
                        .Append(row[0].ToString().Trim()).Append("');").Append("<br />");//System.Environment.NewLine
                }
            }

            ViewBag.Message = sb.ToString();
            return View("GenerateSQL");
        }

        public ActionResult GenerateSQL()
        {
            StringBuilder sb = new StringBuilder("");

            //DataSet ds = SQLiteHelper.ExecuteDataSet("SELECT DISTINCT  [行业代码] ,[行业类型] FROM [Salary] ORDER BY [行业代码]");
            //// "INSERT INTO [Admin].[Industries]([Name],[IndustryCode]) VALUES () ;"

            //foreach (DataRow row in ds.Tables[0].Rows)
            //{
            //    if (row[1].ToString().Trim().Length > 0)
            //    {
            //        sb.Append("INSERT INTO [Admin].[Industries]([Name],[IndustryCode]) VALUES ('")
            //            .Append(row[1].ToString().Trim()).Append("', '")
            //            .Append(row[0].ToString().Trim()).Append("');").Append("<br />");//System.Environment.NewLine
            //    }
            //}

            //DataSet ds = SQLiteHelper.ExecuteDataSet("SELECT DISTINCT [单位性质] FROM [Salary]");
            //// "INSERT INTO [Admin].[Industries]([Name],[IndustryCode]) VALUES () ;"

            //foreach (DataRow row in ds.Tables[0].Rows)
            //{
            //    if (row[0].ToString().Trim().Length > 0)
            //    {
            //        sb.Append("INSERT INTO [Admin].[Categories] ([Name]) VALUES ('")
            //            .Append(row[0].ToString().Trim()).Append("');").Append("<br />");//System.Environment.NewLine
            //    }
            //}


            DataSet dsCompany = SQLiteHelper.ExecuteDataSet("SELECT  DISTINCT [单位代码],[单位],[单位性质], [行业代码] FROM [Salary] ORDER BY [单位代码]");
            List<Category> categories = null;
            List<Industry> industries = null;
            using (SalaryContext ctx = new SalaryContext())
            {
                categories = ctx.Categories.ToList();
                industries = ctx.Industries.ToList();
            }

            foreach (DataRow row in dsCompany.Tables[0].Rows)
            {
                if (row[0].ToString().Trim().Length > 0)
                {
                    var c = categories.FirstOrDefault(x => x.Name == row[2].ToString().Trim());
                    var i = industries.FirstOrDefault(x => x.IndustryCode == row[3].ToString().Trim());


                    sb.Append("INSERT INTO [Salary].[Institutions] ([ID] ,[Name] ,[InstitutionCode] ,[IndustryID] ,[CategoryID]) VALUES ('")
                        .Append(Guid.NewGuid().ToString()).Append("', '")
                        .Append(row[1].ToString().Trim()).Append("', '")
                        .Append(row[0].ToString().Trim()).Append("', ")
                        .Append(i == null ? "NULL" : i.ID.ToString()).Append(", ") // [IndustryID]
                        .Append(c.ID.ToString()).Append(");").Append("<br />");//System.Environment.NewLine
                }
            }

                        //string sql = sb.ToString();
            ViewBag.Message = sb.ToString();
            return View();
        }

        public JsonResult Industry()
        {
            using(SalaryContext ctx = new SalaryContext())
            {
                List<Industry> list = null;
                try
                {
                    list = ctx.Industries.ToList();

                    //var q = from i in ctx.Industries
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                List<IndustryViewModel> models = new List<IndustryViewModel>();
                foreach (Industry i in list)
                {
                    IndustryViewModel m = new IndustryViewModel();
                    m.ID = i.ID;
                    m.Name = i.Name;
                    m.IndustryCode = i.IndustryCode;

                    models.Add(m);
                }

                EasyUIDemo1.Models.EasyUIDataGridWrapper<IndustryViewModel> ll = new Models.EasyUIDataGridWrapper<IndustryViewModel>();
                ll.total = 100;
                ll.rows = models;

                

                JsonResult json = Json(ll);
                return json;
            }
        }

        //public JsonResult Institution(int pageSize, int pageIndex, out int recordCount, out int pageCount)
        //{
        //    string basePath = "";
        //    if (System.Environment.CurrentDirectory == AppDomain.CurrentDomain.BaseDirectory)
        //    {
        //        basePath = AppDomain.CurrentDomain.BaseDirectory;
        //    }
        //    else
        //    {
        //        basePath = AppDomain.CurrentDomain.BaseDirectory + "Bin\\";
        //    }

        //    using (SalaryContext ctx = new SalaryContext())
        //    {
        //        recordCount = ctx.Institutions.Count();
        //        pageCount = recordCount / pageSize;
        //        if (recordCount % pageSize != 0) pageCount += 1;

        //        var q = from i in ctx.Institutions
        //                join c in ctx.Categories on i.Category.ID equals c.ID
        //                join d in ctx.Industries on i.Industry.ID equals d.ID
        //                select new InstitutionViewModel
        //                {
        //                    ID = i.ID,
        //                    Category = c.Name,
        //                    Industry = d.Name,
        //                    InstitutionCode = i.InstitutionCode,
        //                    Name = i.Name,
        //                    OrderNumber = i.OrderNumber,
        //                    QueryCode = i.QueryCode,
        //                    Remarks = i.Remarks,
        //                    RowStatus = i.RowStatus
        //                };

        //        EasyUIDemo1.Models.EasyUIDataGridWrapper<InstitutionViewModel> ll = new Models.EasyUIDataGridWrapper<InstitutionViewModel>();
                
        //        ll.total = recordCount;
        //        ll.rows = q.ToList();
        //        JsonResult json = Json(ll);
        //        return json;
        //    }
        //}


        public ActionResult Institution()
        {
            return View();
        }

        //[ActionName("InstitutionPaged")]

        public JsonResult InstitutionPaged(int pageIndex)
        {
            StreamReader reader = new StreamReader(Request.InputStream);
            string bodyText = reader.ReadToEnd();
            int pageSize = 20;
            //int pageIndex = 0;
            int recordCount, pageCount;
            string basePath = "";
            if (System.Environment.CurrentDirectory == AppDomain.CurrentDomain.BaseDirectory)
            {
                basePath = AppDomain.CurrentDomain.BaseDirectory;
            }
            else
            {
                basePath = AppDomain.CurrentDomain.BaseDirectory + "Bin\\";
            }



            using (SalaryContext ctx = new SalaryContext())
            {
                recordCount = ctx.Institutions.Count();
                pageCount = recordCount / pageSize;
                if (recordCount % pageSize != 0) pageCount += 1;


                var tmp = from p in ctx.Institutions
                          select p;
                tmp = tmp.Where(p => p.InstitutionCode.Contains("ss"));
                tmp = tmp.Where(p => p.Name == "333");

                var list = tmp.ToList();

                var q = (from i in ctx.Institutions
                        join c in ctx.Categories on i.Category.ID equals c.ID
                        join d in ctx.Industries on i.Industry.ID equals d.ID
                        orderby i.ID
                        select new InstitutionViewModel
                        {
                            ID = i.ID,
                            Category = c.Name,
                            Industry = d.Name,
                            InstitutionCode = i.InstitutionCode,
                            Name = i.Name,
                            OrderNumber = i.OrderNumber,
                            QueryCode = i.QueryCode,
                            Remarks = i.Remarks,
                            RowStatus = i.RowStatus
                        }).Skip(pageIndex * pageSize).Take(pageSize);

                

                EasyUIDemo1.Models.EasyUIDataGridWrapper<InstitutionViewModel> ll = new Models.EasyUIDataGridWrapper<InstitutionViewModel>();

                ll.total = recordCount;
                ll.rows = q.ToList();
                JsonResult json = Json(ll, JsonRequestBehavior.AllowGet);
                return json;
            }
        }


        public static IEnumerable<T> GetItems<T>(IEnumerable<T> data, Expression<Func<T, bool>> where, int pageSize, int pageIndex, out int recordCount, out int pageCount)
        {
            if (pageSize < 1) pageSize = 1;
            if (pageIndex < 1) pageIndex = 1;

            Func<T, bool> realWhere = where.Compile();
            recordCount = data.Where(realWhere).Count();
            pageCount = recordCount / pageSize;
            if (recordCount % pageSize != 0) pageCount += 1;

            if (pageIndex > pageCount) pageIndex = pageCount;

            var q = data.Where(realWhere)
                .Select(x => x)
                .Skip((pageIndex - 1) * pageSize)
                .Take(pageSize);

            return q;
        }


        private int m_Count = 0;

        public void Test()
        {
            int value = 0;

            // Does not work, throws compile error
            Action action = () => { if (m_Count < 10) m_Count++; value = m_Count; };

            // Works
            //Action action2 = () => value = delegate()
            //{
            //    if (m_Count < 10)
            //        m_Count++;

            //    return m_Count;
            //};

            // Works
            Action action3 = () => value = m_Count;

            // Works
            Action action4 = () => value = m_Count < 10 ? m_Count++ : 0;

            // Works
            Action action5 = () => value = Increment();

            Action action6 = () => new object();
        }

        public int Increment()
        {
            if (m_Count < 10)
                m_Count++;

            return m_Count;
        }

        public JsonResult GetMetaData()
        {
            //JsonResult json = Json( DictionaryViewModel.GetData(), JsonRequestBehavior.AllowGet);
            JsonResult json = Json(DictionaryViewModel.GetComboboxData(), JsonRequestBehavior.AllowGet);
            return json;

            
        }

        public JsonResult GetIndustry()
        {
            using (SalaryContext ctx = new SalaryContext())
            {
                var q = (from i in ctx.Industries
                         select new ComboxDataItem
                         {
                             id = i.ID,
                             text = i.Name,
                         }).ToList();

                JsonResult json = Json(q, JsonRequestBehavior.AllowGet);
                return json;
            }
        }
        public JsonResult GetCategory()
        {
            using (SalaryContext ctx = new SalaryContext())
            {
                var q = (from i in ctx.Categories
                        select new ComboxDataItem
                        {
                            id = i.ID,
                            text = i.Name,
                        }).ToList();

                JsonResult json = Json(q, JsonRequestBehavior.AllowGet);
                return json;
            }
        }



        public ActionResult GenerateEmpSQL()
        {
            var sql = @"SELECT [编号] AS PersonCode
		,[姓名] AS Name
		,[性别] AS Gender
		,[单位代码] AS InstitutionCode
		,[单位] AS Institution
		,[单位性质] AS Ownership
		,[行业代码] AS IndustryCode
		,[行业类型] AS Industry
		,[出生年月] AS DateOfBirth
		,[身份] AS IdentityType
		,[工作时间] AS WorkingHours
		,[连续工龄日] AS ContinuousDays
		,[不计工龄1] AS ExcludingService1
		,[不计工龄2] AS ExcludingService2
		,[第二学历] AS Education
		,[现职务日] AS SecondDegree
		,[原职务] AS GraduationDay
	FROM [Salary] ORDER BY [单位], [编号] ";
            var insert = " PersonCode, Name, Gender, InstitutionId, InstitutionCode, Institution, OwnershipId, InstitutionOwnership, IndustryId, IndustryCode, Industry, DateOfBirth, IdentityType, WorkingHours, ContinuousDays, ExcludingService1, ExcludingService2, Education,  SecondDegree, GraduationDay ";
            //Employee s = new EasyUIDemo.Model.Employee();

            StringBuilder sb = new StringBuilder("");
            DataSet ds = SQLiteHelper.ExecuteDataSet(sql);
            // "INSERT INTO [Admin].[Industries]([Name],[IndustryCode]) VALUES () ;"

            int categoryId = 0;
            int industryId = 1;
            Guid institutionId = Guid.NewGuid();

            #region Log
            string path = "";
            if (System.Environment.CurrentDirectory == AppDomain.CurrentDomain.BaseDirectory)
            {
                path = AppDomain.CurrentDomain.BaseDirectory;
            }
            else
            {
                path = AppDomain.CurrentDomain.BaseDirectory + "App_Data\\";
            }

            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }
            string logFile = path + DateTime.Now.ToString("yyyyMMdd") + ".log";
            if (!System.IO.File.Exists(logFile))
            {
                System.IO.FileStream log = System.IO.File.Create(logFile);
                log.Close();
            }
            #endregion

            List<DictionaryItem> categories = DictionaryDataUtil.GetData().Where(i => i.Category == "ownership").ToList();
            List<Industry> industries = null;
            List<Institution> institutions = null;
            using (SalaryContext ctx = new SalaryContext())
            {
                industries = ctx.Industries.ToList();
                institutions = ctx.Institutions.ToList();
            }

            SalaryContext db = new SalaryContext();

            StringBuilder tmp = new StringBuilder("");
            int p = 0;
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                p++;
                categoryId = categories.SingleOrDefault(i => i.Name == row["Ownership"].ToString().Trim()).ID;
                if (row["Industry"].ToString().Trim().Length == 0)
                {
                    industryId = 16;
                }
                else
                {
                    industryId = industries.SingleOrDefault(i => i.Name == row["Industry"].ToString().Trim()).ID;
                }
                institutionId = institutions.SingleOrDefault(i => i.Name == row["Institution"].ToString().Trim()).ID;
    
                if (row[1].ToString().Trim().Length > 0)
                {
                    sb.Append("INSERT INTO [Salary].[Employee](" + insert + ") VALUES ('")
                        .Append(row["PersonCode"].ToString().Trim()).Append("', '")
                        .Append(row["Name"].ToString().Trim()).Append("', '")
                        .Append(row["Gender"].ToString().Trim()).Append("', '")
                        .Append(institutionId.ToString()).Append("', '")
                        .Append(row["InstitutionCode"].ToString().Trim()).Append("', '")
                        .Append(row["Institution"].ToString().Trim()).Append("', '")

                        .Append(categoryId).Append("', '")
                        .Append(row["Ownership"].ToString().Trim()).Append("', '")
                        .Append(industryId).Append("', '")
                        .Append(row["IndustryCode"].ToString().Trim()).Append("', '")
                        .Append(row["Industry"].ToString().Trim()).Append("', '")
                        .Append(row["DateOfBirth"].ToString().Trim().Length > 0 ? row["DateOfBirth"].ToString().Trim() + "01" :"").Append("', '")

                        .Append(row["IdentityType"].ToString().Trim()).Append("', '")
                        .Append(row["WorkingHours"].ToString().Trim().Length > 0 ? row["WorkingHours"].ToString().Trim() + "01" : "").Append("', '")
                        .Append(row["ContinuousDays"].ToString().Trim().Length > 0 ? row["ContinuousDays"].ToString().Trim() + "01" : "").Append("', '")
                        .Append(row["ExcludingService1"].ToString().Trim()).Append("', '")
                        .Append(row["ExcludingService2"].ToString().Trim()).Append("', '")
                        .Append(row["Education"].ToString().Trim()).Append("', '")
                        .Append(row["SecondDegree"].ToString().Trim()).Append("', '")
                        .Append(row["GraduationDay"].ToString().Trim().Length > 0 ? row["GraduationDay"].ToString().Trim() + "01" : "").Append("');").Append(System.Environment.NewLine);//System.Environment.NewLine<br />
                }

                if (row[1].ToString().Trim().Length > 0)
                {
                    tmp.Append("INSERT INTO [Salary].[Employee](" + insert + ") VALUES ('")
                        .Append(row["PersonCode"].ToString().Trim()).Append("', '")
                        .Append(row["Name"].ToString().Trim()).Append("', '")
                        .Append(row["Gender"].ToString().Trim()).Append("', '")
                        .Append(institutionId.ToString()).Append("', '")
                        .Append(row["InstitutionCode"].ToString().Trim()).Append("', '")
                        .Append(row["Institution"].ToString().Trim()).Append("', '")

                        .Append(categoryId).Append("', '")
                        .Append(row["Ownership"].ToString().Trim()).Append("', '")
                        .Append(industryId).Append("', '")
                        .Append(row["IndustryCode"].ToString().Trim()).Append("', '")
                        .Append(row["Industry"].ToString().Trim()).Append("', '")
                        .Append(row["DateOfBirth"].ToString().Trim().Length > 0 ? row["DateOfBirth"].ToString().Trim() + "01" :"").Append("', '")

                        .Append(row["IdentityType"].ToString().Trim()).Append("', '")
                        .Append(row["WorkingHours"].ToString().Trim().Length > 0 ? row["WorkingHours"].ToString().Trim() + "01" : "").Append("', '")
                        .Append(row["ContinuousDays"].ToString().Trim().Length > 0 ? row["ContinuousDays"].ToString().Trim() + "01" : "").Append("', '")
                        .Append(row["ExcludingService1"].ToString().Trim()).Append("', '")
                        .Append(row["ExcludingService2"].ToString().Trim()).Append("', '")
                        .Append(row["Education"].ToString().Trim()).Append("', '")
                        .Append(row["SecondDegree"].ToString().Trim()).Append("', '")
                        .Append(row["GraduationDay"].ToString().Trim().Length > 0 ? row["GraduationDay"].ToString().Trim() + "01" : "").Append("');").Append(System.Environment.NewLine);//System.Environment.NewLine<br />
                }

                if (p / 100 == 0)
                {
                    db.Database.ExecuteSqlCommand(tmp.ToString());
                    tmp.Length = 0;
                    p = 0;
                }
            }

            if (tmp.Length > 0)
            {
                db.Database.ExecuteSqlCommand(tmp.ToString());
                tmp.Length = 0;
                p = 0;
            }
            db.Dispose();
            

            //WriteLog(sb.ToString());

            ViewBag.Message = "";// sb.ToString();
            return View("GenerateSQL");
        }

        private static void WriteLog(string message)
        {

            #region Log
            string path = "";
            if (System.Environment.CurrentDirectory == AppDomain.CurrentDomain.BaseDirectory)
            {
                path = AppDomain.CurrentDomain.BaseDirectory;
            }
            else
            {
                path = AppDomain.CurrentDomain.BaseDirectory + "App_Data\\";
            }

            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }
            string logFile = path + DateTime.Now.ToString("yyyyMMdd") + ".log";
            if (!System.IO.File.Exists(logFile))
            {
                System.IO.FileStream log = System.IO.File.Create(logFile);
                log.Close();
            }
            #endregion

            using (System.IO.StreamWriter w = System.IO.File.AppendText(logFile))
            {
                w.WriteLine(message);
            }
        }
    }
}
